package com.orange.util;

import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FeedBackExport {

    private static final Logger log = Logger.getLogger(FeedBackExport.class);

    public void exportXLS(HttpServletResponse response, List<List<String>> list, String filename)
            throws Exception {

        log.info("============数据导入excel开始===============");
        // 1.创建一个 work
        // HSSFWorkbook workbook = new HSSFWorkbook();
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2.创建一个 sheet
        XSSFSheet sheet = workbook.createSheet("sheet");
        //设置单元格样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();//新建单元格样式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
        /*
         * // 3.获取实体类的所有属性，返回Field数组 Field[] field = list.get(0).getClass().getDeclaredFields();
         */
        // 4.设置所有列宽
       /* for (int i = 0; i < list.get(0).size(); i++) {
            sheet.setColumnWidth(i, 5000);
        }*/
        /*
         * // 5.第一行目录添加 HSSFRow row0=sheet.createRow(0); for (int j = 0; j < list.get(0).size();
         * j++) { // 获取属性的名字 String name = list.get(0).get(j);
         * row0.createCell(j).setCellValue(name); }
         */
        //合并单元格
        CellRangeAddress cellRange1 = new CellRangeAddress(0, 0, 0, 5);
        CellRangeAddress cellRange2 = new CellRangeAddress(1, 1, 0, 5);
        CellRangeAddress cellRange3 = new CellRangeAddress(6, 8, 0, 5);
        CellRangeAddress cellRange4 = new CellRangeAddress(10, 12, 0, 5);
        CellRangeAddress cellRange5 = new CellRangeAddress(14, 16, 0, 5);
        CellRangeAddress cellRange6 = new CellRangeAddress(18, 20, 0, 5);
        
        CellRangeAddress cellRange7 = new CellRangeAddress(5, 5, 0, 5);
        CellRangeAddress cellRange8 = new CellRangeAddress(9, 9, 0, 5);
        CellRangeAddress cellRange9 = new CellRangeAddress(13, 13, 0, 5);
        CellRangeAddress cellRange10 = new CellRangeAddress(17, 17, 0, 5);
        CellRangeAddress cellRange11 = new CellRangeAddress(21, 21, 0, 5);
        
        CellRangeAddress cellRange12 = new CellRangeAddress(2, 2, 0, 1);
        CellRangeAddress cellRange13 = new CellRangeAddress(2, 2, 2, 5);
        CellRangeAddress cellRange14 = new CellRangeAddress(3, 3, 0, 1);
        CellRangeAddress cellRange15 = new CellRangeAddress(3, 3, 2, 5);
        CellRangeAddress cellRange16 = new CellRangeAddress(4, 4, 0, 1);
        CellRangeAddress cellRange17 = new CellRangeAddress(4, 4, 2, 5);
        
        CellRangeAddress cellRange18 = new CellRangeAddress(22, 24, 0, 5);
        sheet.addMergedRegion(cellRange1);
        sheet.addMergedRegion(cellRange2);
        sheet.addMergedRegion(cellRange3);
        sheet.addMergedRegion(cellRange4);
        sheet.addMergedRegion(cellRange5);
        sheet.addMergedRegion(cellRange6);
        sheet.addMergedRegion(cellRange7);
        sheet.addMergedRegion(cellRange8);
        sheet.addMergedRegion(cellRange9);
        sheet.addMergedRegion(cellRange10);
        sheet.addMergedRegion(cellRange11);
        sheet.addMergedRegion(cellRange12);
        sheet.addMergedRegion(cellRange13);
        sheet.addMergedRegion(cellRange14);
        sheet.addMergedRegion(cellRange15);
        sheet.addMergedRegion(cellRange16);
        sheet.addMergedRegion(cellRange17);
        sheet.addMergedRegion(cellRange18);
        // 6.遍历对象集合 填充是数据
        for (int a = 0; a < list.size(); a++) {
            XSSFRow row = sheet.createRow(a);
            for (int j = 0; j < list.get(a).size(); j++) {
                String name = list.get(a).get(j);
                row.createCell(j).setCellValue(name);
                if(a <= 4){
                	row.getCell(j).setCellStyle(cellStyle);
                }
            }
        }
        // 7.设置reponse参数
        String fileName = filename + ".xlsx";
        response.setHeader("Content-Disposition", "inline; filename="
                + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));
        // 确保发送的当前文本格式
        response.setContentType("application/vnd.ms-excel");

        // 8.输出流
        write(response, sheet);
        log.info("============数据导入excel结束===============");
    }

    public static void write(HttpServletResponse response, XSSFSheet sheet) {

        try {
            // 输出
            ServletOutputStream outputStream = response.getOutputStream();
            // 写入
            sheet.getWorkbook().write(outputStream);
            // 清除缓存
            outputStream.flush();

        } catch (Exception e) {
            log.error("数据导出失败!");
        }
    }
}
